'use strict';

const BaseService = require('./base');
const md5 = require('md5');

class UserService extends BaseService {
  get _model() {
    return 'User';
  }

  async findOne({ id, userName, password }) {
    const { app } = this;
    const where = this.formatWhere({ exact: { id, userName } });
    const include = [
      {
        model: app.model.UserAndRole,
        include: [
          {
            model: app.model.Role,
            attributes: {
              exclude: app.exclude,
            },
          },
        ],
      },
      {
        model: app.model.UserInfo,
        attributes: [ 'avatar' ],
      },
    ];
    if (password !== undefined) {
      where.password = md5(password + app.config.salt);
    } else {
      include.push({
        model: app.model.UserInfo,
        attributes: {
          exclude: app.exclude,
        },
        include: [
          {
            model: app.model.Area,
            attributes: {
              exclude: app.exclude,
            },
          },
        ],
      });
    }
    const attributes = {
      exclude: [ 'password', 'deletedAt' ],
    };
    return this._findOne({ where, include, attributes });
  }

  async create({ userName, password, nickName }) {
    const { app } = this;
    return this._create({
      userName, password: md5(password + app.config.salt), nickName,
    });
  }

  async update({ password, nickName, id }) {
    const { app } = this;
    const body = { password: md5(password + app.config.salt), nickName };
    const where = { id };
    return this._update({ body, where });
  }

  async findList({ userName, nickName, rid, prop, sort, pageIndex, pageSize, createdAt }) {
    const { app } = this;
    const where = this.formatWhere({ dim: { userName, nickName }, range: { createdAt } });
    const whereRole = this.formatWhere({ exact: { rid } });
    const order = this.formatOrder(prop, sort);
    const attributes = {
      exclude: app.excludeDate.concat([ 'password' ]),
    };
    const include = [
      {
        model: app.model.UserAndRole,
        where: whereRole,
        include: [
          {
            model: app.model.Role,
            attributes: [ 'name' ],
          },
        ],
      },
    ];
    return this._findList({ pageIndex, pageSize, where, order, attributes, include });
  }

  async findAll() {
    return this._findAll();
  }

  async statisticalUser({ startTime, areaId }) {
    if (!startTime) {
      startTime = new Date().getFullYear();
    }
    let where = 'YEAR(`user`.`createdAt`) = ?';
    if (areaId) {
      where = 'YEAR(`user`.`createdAt`) = ? AND FIND_IN_SET(`userinfo`.`areaId`, ?)';
    }
    const sql = `SELECT
          SUM(CASE WHEN MONTH(\`user\`.\`createdAt\`) = 1 THEN 1 ELSE 0 END) AS "1",
          SUM(CASE WHEN MONTH(\`user\`.\`createdAt\`) = 2 THEN 1 ELSE 0 END) AS "2",
          SUM(CASE WHEN MONTH(\`user\`.\`createdAt\`) = 3 THEN 1 ELSE 0 END) AS "3",
          SUM(CASE WHEN MONTH(\`user\`.\`createdAt\`) = 4 THEN 1 ELSE 0 END) AS "4",
          SUM(CASE WHEN MONTH(\`user\`.\`createdAt\`) = 5 THEN 1 ELSE 0 END) AS "5",
          SUM(CASE WHEN MONTH(\`user\`.\`createdAt\`) = 6 THEN 1 ELSE 0 END) AS "6",
          SUM(CASE WHEN MONTH(\`user\`.\`createdAt\`) = 7 THEN 1 ELSE 0 END) AS "7",
          SUM(CASE WHEN MONTH(\`user\`.\`createdAt\`) = 8 THEN 1 ELSE 0 END) AS "8",
          SUM(CASE WHEN MONTH(\`user\`.\`createdAt\`) = 9 THEN 1 ELSE 0 END) AS "9",
          SUM(CASE WHEN MONTH(\`user\`.\`createdAt\`) = 10 THEN 1 ELSE 0 END) AS "10",
          SUM(CASE WHEN MONTH(\`user\`.\`createdAt\`) = 11 THEN 1 ELSE 0 END) AS "11",
          SUM(CASE WHEN MONTH(\`user\`.\`createdAt\`) = 12 THEN 1 ELSE 0 END) AS "12"
      FROM (SELECT \`user\`.\`createdAt\` FROM \`user\` LEFT JOIN \`userinfo\` ON \`userinfo\`.\`uid\` = \`user\`.\`id\` WHERE ${where}) AS \`user\``;
    return await this.querySelect(sql, [ startTime, areaId ]);
  }
}

module.exports = UserService;
